package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantmaterialCandiatecategoryDao;
import com.ph.irp.model.PBaseAssistantmaterialCandiatecategory;
import com.ph.util.DBUtil;

public class PBaseAssistantmaterialCandiatecategoryDaoImpl implements
		PBaseAssistantmaterialCandiatecategoryDao {
	private ComboPooledDataSource dataSource;

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addCandiateCategory(
			PBaseAssistantmaterialCandiatecategory candiateCategory)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append("insert into p_base_assistantmaterial_candiatecategory (CandiateCategoryCode,CandiateCategoryName)values(");
		sb.append("'")
				.append(candiateCategory.getCandiateCategoryCode().trim())
				.append("',");
		sb.append("'")
				.append(candiateCategory.getCandiateCategoryName().trim())
				.append("')");
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public void deleteCandiateCategory(int candiateCategoryId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sql = "delete from p_base_assistantmaterial_candiatecategory where CandiateCategoryID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, candiateCategoryId);
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@Override
	public PBaseAssistantmaterialCandiatecategory getCandiateCategoryById(
			int candiateCategoryId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseAssistantmaterialCandiatecategory candiateCategory = null;
		String sql = "select * from p_base_assistantmaterial_candiatecategory where CandiateCategoryID=?";
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			pStmt.setInt(1, candiateCategoryId);
			rs = pStmt.executeQuery();
			if (rs.next()) {
				candiateCategory = new PBaseAssistantmaterialCandiatecategory();
				candiateCategory.setCandiateCategoryId(rs
						.getInt("CandiateCategoryID"));
				candiateCategory.setCandiateCategoryCode(rs
						.getString("CandiateCategoryCode"));
				candiateCategory.setCandiateCategoryName(rs
						.getString("CandiateCategoryName"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return candiateCategory;
	}

	@Override
	public List<PBaseAssistantmaterialCandiatecategory> getCandiateCategoryList()
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialCandiatecategory> list = new ArrayList<PBaseAssistantmaterialCandiatecategory>();

		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_assistantmaterial_candiatecategory");
		// if(candiateCategory!=null){
		// if(candiateCategory.getCandiateCategoryCode() != null &&
		// !"".equals(candiateCategory.getCandiateCategoryCode())){
		// sb.append(" and CandiateCategoryCode like '%");
		// sb.append(candiateCategory.getCandiateCategoryCode());
		// sb.append("%'");
		// }
		// if(candiateCategory.getCandiateCategoryName() != null &&
		// !"".equals(candiateCategory.getCandiateCategoryName())){
		// sb.append(" and CandiateCategoryName like '%");
		// sb.append(candiateCategory.getCandiateCategoryName());
		// sb.append("%'");
		// }
		// }
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialCandiatecategory pbac = new PBaseAssistantmaterialCandiatecategory();
				pbac.setCandiateCategoryId(rs.getInt("CandiateCategoryID"));
				pbac.setCandiateCategoryCode(rs
						.getString("CandiateCategoryCode"));
				pbac.setCandiateCategoryName(rs
						.getString("CandiateCategoryName"));
				list.add(pbac);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

	@SuppressWarnings("finally")
	@Override
	public boolean isSame(String candiateCategoryCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		Boolean b = null;

		String sql = "select * from p_base_assistantmaterial_candiatecategory where CandiateCategoryCode='"
				+ candiateCategoryCode.trim() + "'";

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if (rs.next()) {
				b = true;
			} else {
				b = false;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);

		}
		return b;
	}

	@Override
	public void updateCandiateCategory(
			PBaseAssistantmaterialCandiatecategory candiateCategory)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb
				.append("update p_base_assistantmaterial_candiatecategory set CandiateCategoryCode='");
		sb.append(candiateCategory.getCandiateCategoryCode().trim()).append(
				"',CandiateCategoryName='");
		sb.append(candiateCategory.getCandiateCategoryName().trim())
				.append("'");
		sb.append(" where CandiateCategoryID=").append(
				candiateCategory.getCandiateCategoryId());

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PBaseAssistantmaterialCandiatecategory> getCandiateCategoryByCode(
			String Code) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialCandiatecategory> list = new ArrayList();
		String sql = "select * from p_base_assistantmaterial_candiatecategory where CandiateCategoryCode like '%"
				+ Code + "%'";

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialCandiatecategory p = new PBaseAssistantmaterialCandiatecategory();
				p.setCandiateCategoryCode(rs.getString("CandiateCategoryCode"));
				p.setCandiateCategoryName(rs.getString("CandiateCategoryName"));
				p.setCandiateCategoryId(rs.getInt("CandiateCategoryID"));
				list.add(p);
			}
			return list;
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<PBaseAssistantmaterialCandiatecategory> getCandiateCategoryByName(
			String name) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sql = "select * from p_base_assistantmaterial_candiatecategory where CandiateCategoryName like '%"
				+ name + "%'";
		List<PBaseAssistantmaterialCandiatecategory> list = new ArrayList();
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialCandiatecategory p = new PBaseAssistantmaterialCandiatecategory();
				p.setCandiateCategoryCode(rs.getString("CandiateCategoryCode"));
				p.setCandiateCategoryName(rs.getString("CandiateCategoryName"));
				p.setCandiateCategoryId(rs.getInt("CandiateCategoryID"));
				list.add(p);
			}
			return list;
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List getCandiateCategoryByNameAndCode(String name, String Code)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseAssistantmaterialCandiatecategory> list = new ArrayList<PBaseAssistantmaterialCandiatecategory>();

		StringBuilder sb = new StringBuilder();
		sb
				.append("select * from p_base_assistantmaterial_candiatecategory where");
		sb.append(" CandiateCategoryCode like '%");
		sb.append(Code);
		sb.append("%'");
		sb.append(" and CandiateCategoryName like '%");
		sb.append(name);
		sb.append("%'");

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while (rs.next()) {
				PBaseAssistantmaterialCandiatecategory pbac = new PBaseAssistantmaterialCandiatecategory();
				pbac.setCandiateCategoryId(rs.getInt("CandiateCategoryID"));
				pbac.setCandiateCategoryCode(rs
						.getString("CandiateCategoryCode"));
				pbac.setCandiateCategoryName(rs
						.getString("CandiateCategoryName"));
				list.add(pbac);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			DBUtil.setNullObject(con, pStmt, rs);
		}
		return list;
	}

}
